package com.ias.agdis_r.db;

import java.io.IOException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.ias.agdis_r.bean.task.LuggageBarCode;
import com.ias.agdis_r.manager.DataManager;
import com.ias.agdis_r.manager.JavaBeanManager;
import com.ias.agdis_r.module.ParameterModel;
import com.ias.agdis_r.terminalmanager.restful.task.vo.SubmitTaskProcessRequestVO;
import com.ias.agdis_r.util.S;

import org.codehaus.jackson.type.TypeReference;

public class BoxDBHelper extends SQLiteOpenHelper {
    private static final String DB_NAME = "temp_data.db3";
    private static final int DB_VERSION = 2;
    private Cursor cursor = null;
    private SQLiteDatabase db = getWritableDatabase();

    public static final String TABLE_FLIGHT = "boxflight";
    public static final String TABLE_TASK = "boxtask";
    public static final String TABLE_TASKPROCESS = "taskprocess";
    public static final String TABLE_MESSAGE = "com_ias_agdis_r_db_message_MyMessageBean";
    public static final String TABLE_LUGGAGEBARCODE = "luggagebarcode";
    public static final String TABLE_TASKCHOOSEMEMBER = "taskchoosemember";

    public BoxDBHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
    }

    public List<Map<String, Object>> getData(String tableName, String sArray[], String sWhere, String order) {
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        if (sArray.length < 1)
            return null;
        Map<String, Object> map = new HashMap<String, Object>();
        try {
            // 查询test_listview数据
            cursor = db.query(tableName, new String[]{}, sWhere, null, null, null, order);
            // 通过强大的cursor把数据库的资料一行一行地读取出来
            while (cursor.moveToNext()) {
                map = new HashMap<String, Object>();
                for (int x = 0; x < sArray.length; x++) {
                    map.put(sArray[x], (getTableString(sArray[x])).trim());
                }
                list.add(map);
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        return list;
    }

    private String getTableString(String sColIndex) {
        String val = cursor.getString(cursor.getColumnIndex(sColIndex));
        if (val != null) {
            return val;
        } else {
            return "";
        }
    }

    /**
     * 获取消息盒子表中的未读信息
     */
    public int getCount(String[] enb) {
        int count = 0;
        cursor = db.rawQuery("select count(1) from " + TABLE_TASK + " where ischeck='0' and enb=?", enb);
        cursor.moveToFirst();
        count = cursor.getInt(0);
        cursor = db.rawQuery("select count(1) from " + TABLE_FLIGHT + " where ischeck='0' and enb=?", enb);
        cursor.moveToFirst();
        count = count + cursor.getInt(0);
        cursor = db.rawQuery(
                "select count(1) from " + TABLE_MESSAGE + " where readed is null and sem is not '' and user_name=?",
                enb);
        cursor.moveToFirst();
        count = count + cursor.getInt(0);
        return count;
    }

    /**
     * 向boxtask表中插入数据
     *
     * @param fln
     * @param fke
     */
    public void setTaskData(String tnb, String msg, String serviceId, String time, String fke, String fln) {
        ContentValues values = new ContentValues();
        values.put("tnb", tnb);
        values.put("msg", msg);
        values.put("type", serviceId);
        if (S.isEmpty(time)) {
            values.put("time", S.getNowTime3());
        } else {
            values.put("time", time);
        }
        values.put("enb", JavaBeanManager.getInstance().getUserinfo().getEnb());
        values.put("ischeck", 0);
        values.put("fke", fke);
        values.put("fln", fln);
        db.insert(TABLE_TASK, null, values);
    }

    /**
     * 向boxflight表中插入数据
     */
    public void setFlightData(String fke, String msg, String serviceId, String time) {
        ContentValues values = new ContentValues();
        values.put("fke", fke);
        values.put("msg", msg);
        values.put("type", serviceId);
        if (time == null) {
            values.put("time", S.getNowTime3());
        } else {
            values.put("time", time);
        }
        values.put("enb", JavaBeanManager.getInstance().getUserinfo().getEnb());
        values.put("ischeck", 0);
        db.insert(TABLE_FLIGHT, null, values);
    }

    /**
     * 忽略任务和航班消息
     */
    public void setIgnore(String tableName, String columnName, String[] _id) {
        ContentValues values = new ContentValues();
        values.put(columnName, 2);
        db.update(tableName, values, "_id in(?)", _id);
        DataManager.getInstance().subtractUnCheckNumber();
    }

    /**
     * 忽略短语消息
     */
    public void setIgnoreMessage(String tableName, String columnName, String[] _id) {
        ContentValues values = new ContentValues();
        values.put(columnName, 2);
        int rows = db.update(tableName, values, "_id in(?) and ack != 'Y'", _id);
        if (rows == 1) {
            DataManager.getInstance().subtractUnCheckNumber();
        }
    }

    /**
     * 删除3天前的数据
     */
    public void deleteDate3(String tableName, String timeName, String[] date) {
        db.delete(tableName, timeName + " <= ?", date);
    }

    /**
     * 删除未读的新下发任务信息
     */
    public void deleteUncheckTTSD() {
        int i = db.delete(TABLE_TASK, "type = 'TTSD' and ischeck ='0'", null);
    }

//    /**
//     * 写入任务进展数据
//     */
//    public void setTaskProcessDate(String tnb, String title, String msg) {
//        ContentValues values = new ContentValues();
//        values.put("tnb", tnb);
//        values.put("title", title);
//        values.put("msg", msg);
//        values.put("time", S.getNowTime3());
//        values.put("enb", JavaBeanManager.getInstance().getUserinfo().getEnb());
//        db.insert(TABLE_TASKPROCESS, null, values);
//        db.close();
//    }

    /**
     * 获取当前信息是否已读
     */
    public int isAllCheck(String sql, String[] selectionArgs) {
        int count = -1;
        cursor = db.rawQuery(sql, selectionArgs);
        cursor.moveToFirst();
        count = cursor.getInt(0);
        return count;
    }

    /**
     * 标记为已读
     */
    public void setCheck(String tableName, String whereClause,
                         String[] whereArgs) {
        ContentValues values = new ContentValues();
        values.put("ischeck", 1);
        int i = db.update(tableName, values, whereClause, whereArgs);
    }

    public void deleteTaskProcess(String[] tnb) {
        db.delete(TABLE_TASKPROCESS, "tnb = ?", tnb);
    }

    public void closeDB() {
        db.close();
    }

    public long saveLuggageBarCode(LuggageBarCode luggageBarCode, boolean check) {
        if (check) {
            if (hasSameLuggageBarCode(luggageBarCode)) {
                return -2;
            }
        }
        ContentValues values = new ContentValues();
        values.put("fkey", luggageBarCode.getFkey());
        values.put("flop", luggageBarCode.getFlop());
        values.put("flno", luggageBarCode.getFlno());
        values.put("adid", luggageBarCode.getAdid());
        values.put("tnb", luggageBarCode.getTnb());
        values.put("enb", luggageBarCode.getEnb());
        values.put("barcode", luggageBarCode.getBarcode());
        values.put("scanTime", luggageBarCode.getScanTime());
        values.put("state", luggageBarCode.getState());
        return db.insert(TABLE_LUGGAGEBARCODE, null, values);
    }

    private boolean hasSameLuggageBarCode(LuggageBarCode luggageBarCode) {
        String sql = "select count(1) from " + TABLE_LUGGAGEBARCODE + " where fkey = ? and tnb = ? and enb = ? and barcode = ?";
        Cursor cursor = db.rawQuery(sql, new String[] {luggageBarCode.getFkey(), luggageBarCode.getTnb(), luggageBarCode.getEnb(), luggageBarCode.getBarcode()});
        cursor.moveToFirst();
        int count = cursor.getInt(0);
        cursor.close();
        return count > 0;
    }

    public void setMultiLuggageBarCodeState(List<LuggageBarCode> list, String state) {
        db.beginTransaction();
        try {
            for (LuggageBarCode luggageBarCode : list) {
                ContentValues values = new ContentValues();
                values.put("state", S.isEmpty(state) ? luggageBarCode.getState() : state);
                db.update(TABLE_LUGGAGEBARCODE, values, "tnb = ? and enb = ? and barcode = ?",
                        new String[]{luggageBarCode.getTnb(), luggageBarCode.getEnb(), luggageBarCode.getBarcode()});
            }
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
    }

    public void setOneLuggageBarCodeState(LuggageBarCode luggageBarCode) {
        ContentValues values = new ContentValues();
        values.put("state", luggageBarCode.getState());
        db.update(TABLE_LUGGAGEBARCODE, values, "tnb = ? and enb = ? and barcode = ?",
                new String[]{luggageBarCode.getTnb(), luggageBarCode.getEnb(), luggageBarCode.getBarcode()});
    }

    public void deleteOneLuggageBarCode(LuggageBarCode luggageBarCode) {
        db.delete(TABLE_LUGGAGEBARCODE, "tnb = ? and enb = ? and barcode = ?",
                new String[]{luggageBarCode.getTnb(), luggageBarCode.getEnb(), luggageBarCode.getBarcode()});
    }

    public List<LuggageBarCode> getSendLuggageBarCode(){
        List<LuggageBarCode> list = new ArrayList<>();
        try {
            String sql = "select * from " + BoxDBHelper.TABLE_LUGGAGEBARCODE + " where enb = '" + JavaBeanManager.getInstance().getUserinfo().getEnb()
                    + "' and state = '" + LuggageBarCode.STATE_ADD
                    + "' or state = '" + LuggageBarCode.STATE_SEND_FAILED
                    + "' or state = '" + LuggageBarCode.STATE_SENDING + "' order by scanTime limit 50";
            Cursor cursor = db.rawQuery(sql, null);
            while (cursor.moveToNext()) {
                String id = cursor.getString(cursor.getColumnIndex("_id"));
                String fkey = cursor.getString(cursor.getColumnIndex("fkey"));
                String flop = cursor.getString(cursor.getColumnIndex("flop"));
                String flno = cursor.getString(cursor.getColumnIndex("flno"));
                String adid = cursor.getString(cursor.getColumnIndex("adid"));
                String tnb = cursor.getString(cursor.getColumnIndex("tnb"));
                String enb = cursor.getString(cursor.getColumnIndex("enb"));
                String barcode = cursor.getString(cursor.getColumnIndex("barcode"));
                String scanTime = cursor.getString(cursor.getColumnIndex("scanTime"));
                String state = cursor.getString(cursor.getColumnIndex("state"));
                LuggageBarCode luggageBarCode = new LuggageBarCode();
                luggageBarCode.setId(id);
                luggageBarCode.setFkey(fkey);
                luggageBarCode.setFlop(flop);
                luggageBarCode.setFlno(flno);
                luggageBarCode.setAdid(adid);
                luggageBarCode.setTnb(tnb);
                luggageBarCode.setEnb(enb);
                luggageBarCode.setBarcode(barcode);
                luggageBarCode.setScanTime(scanTime);
                luggageBarCode.setState(state);
                list.add(luggageBarCode);
            }
            cursor.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public int getUploadingLuggageBarCodeCount(String tnbStr) {
        int count = 0;
        try {
            String sql = "select count(1) from " + BoxDBHelper.TABLE_LUGGAGEBARCODE + " where enb = '" + JavaBeanManager.getInstance().getUserinfo().getEnb()
                    + "' and tnb = '" + tnbStr
                    + "' and (state = '" + LuggageBarCode.STATE_ADD
                    + "' or state = '" + LuggageBarCode.STATE_SENDING
                    + "' or state = '" + LuggageBarCode.STATE_SEND_FAILED + "')";
            Cursor cursor = db.rawQuery(sql, null);
            cursor.moveToFirst();
            count = cursor.getInt(0);
            cursor.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return count;
    }

    public int getFinishLuggageBarCodeCount(String tnbStr) {
        int count = 0;
        try {
            String sql = "select count(1) from " + BoxDBHelper.TABLE_LUGGAGEBARCODE + " where enb = '" + JavaBeanManager.getInstance().getUserinfo().getEnb()
                    + "' and tnb = '" + tnbStr
                    + "' and (state = '" + LuggageBarCode.STATE_SENT
                    + "' or state = '" + LuggageBarCode.STATE_DELETE_FAILED + "')";
            Cursor cursor = db.rawQuery(sql, null);
            cursor.moveToFirst();
            count = cursor.getInt(0);
            cursor.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return count;
    }

    public List<LuggageBarCode> getUploadingLuggageBarCode(String tnbStr){
        List<LuggageBarCode> list = new ArrayList<>();
        try {
            String sql = "select * from " + BoxDBHelper.TABLE_LUGGAGEBARCODE + " where enb = '" + JavaBeanManager.getInstance().getUserinfo().getEnb()
                    + "' and tnb = '" + tnbStr
                    + "' and (state = '" + LuggageBarCode.STATE_ADD
                    + "' or state = '" + LuggageBarCode.STATE_SENDING
                    + "' or state = '" + LuggageBarCode.STATE_SEND_FAILED + "') order by _id desc";
            Cursor cursor = db.rawQuery(sql, null);
            while (cursor.moveToNext()) {
                String id = cursor.getString(cursor.getColumnIndex("_id"));
                String fkey = cursor.getString(cursor.getColumnIndex("fkey"));
                String flop = cursor.getString(cursor.getColumnIndex("flop"));
                String flno = cursor.getString(cursor.getColumnIndex("flno"));
                String adid = cursor.getString(cursor.getColumnIndex("adid"));
                String tnb = cursor.getString(cursor.getColumnIndex("tnb"));
                String enb = cursor.getString(cursor.getColumnIndex("enb"));
                String barcode = cursor.getString(cursor.getColumnIndex("barcode"));
                String scanTime = cursor.getString(cursor.getColumnIndex("scanTime"));
                String state = cursor.getString(cursor.getColumnIndex("state"));
                LuggageBarCode luggageBarCode = new LuggageBarCode();
                luggageBarCode.setId(id);
                luggageBarCode.setFkey(fkey);
                luggageBarCode.setFlop(flop);
                luggageBarCode.setFlno(flno);
                luggageBarCode.setAdid(adid);
                luggageBarCode.setTnb(tnb);
                luggageBarCode.setEnb(enb);
                luggageBarCode.setBarcode(barcode);
                luggageBarCode.setScanTime(scanTime);
                luggageBarCode.setState(state);
                list.add(luggageBarCode);
            }
            cursor.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public List<LuggageBarCode> getFinishLuggageBarCode(String tnbStr){
        List<LuggageBarCode> list = new ArrayList<>();
        try {
            String sql = "select * from " + BoxDBHelper.TABLE_LUGGAGEBARCODE + " where enb = '" + JavaBeanManager.getInstance().getUserinfo().getEnb()
                    + "' and tnb = '" + tnbStr
                    + "' and (state = '" + LuggageBarCode.STATE_SENT
                    + "' or state = '" + LuggageBarCode.STATE_DELETE_FAILED +  "') order by _id desc";
            Cursor cursor = db.rawQuery(sql, null);
            while (cursor.moveToNext()) {
                String id = cursor.getString(cursor.getColumnIndex("_id"));
                String fkey = cursor.getString(cursor.getColumnIndex("fkey"));
                String flop = cursor.getString(cursor.getColumnIndex("flop"));
                String flno = cursor.getString(cursor.getColumnIndex("flno"));
                String adid = cursor.getString(cursor.getColumnIndex("adid"));
                String tnb = cursor.getString(cursor.getColumnIndex("tnb"));
                String enb = cursor.getString(cursor.getColumnIndex("enb"));
                String barcode = cursor.getString(cursor.getColumnIndex("barcode"));
                String scanTime = cursor.getString(cursor.getColumnIndex("scanTime"));
                String state = cursor.getString(cursor.getColumnIndex("state"));
                LuggageBarCode luggageBarCode = new LuggageBarCode();
                luggageBarCode.setId(id);
                luggageBarCode.setFkey(fkey);
                luggageBarCode.setFlop(flop);
                luggageBarCode.setFlno(flno);
                luggageBarCode.setAdid(adid);
                luggageBarCode.setTnb(tnb);
                luggageBarCode.setEnb(enb);
                luggageBarCode.setBarcode(barcode);
                luggageBarCode.setScanTime(scanTime);
                luggageBarCode.setState(state);
                list.add(luggageBarCode);
            }
            cursor.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public void saveQueryLuggageResponse(List<LuggageBarCode> list, String tnb) {
        db.beginTransaction();
        try {
            for (LuggageBarCode luggageBarCode : list) {
                luggageBarCode.setState(LuggageBarCode.STATE_SENT);
                String sql = "INSERT OR REPLACE INTO " + BoxDBHelper.TABLE_LUGGAGEBARCODE +" (fkey, flop, flno, adid, tnb, enb, barcode, scanTime, state) values ('"
                        + luggageBarCode.getFkey() + "', '" + luggageBarCode.getFlop() + "', '" + luggageBarCode.getFlno() + "', '" + luggageBarCode.getAdid() + "', '"
                        + luggageBarCode.getTnb() + "', '" + luggageBarCode.getEnb() + "', '" + luggageBarCode.getBarcode() + "', '" + luggageBarCode.getScanTime() + "', '"
                        + luggageBarCode.getState() + "')";
                db.execSQL(sql);
            }
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
    }

    public List<SubmitTaskProcessRequestVO.TaskUser> getTaskUsers(String tnb) {
        List<SubmitTaskProcessRequestVO.TaskUser> taskUsers = new ArrayList<>();
        String sql = "select members from " + TABLE_TASKCHOOSEMEMBER + " where tnb = ? and enb = ?";
        Cursor cursor = db.rawQuery(sql, new String[] {tnb, JavaBeanManager.getInstance().getUserinfo().getEnb()});
        if (cursor.moveToFirst()) {
            String usersJson = cursor.getString(cursor.getColumnIndex("members"));
            try {
                taskUsers = DataManager.getInstance().getMapper().readValue(usersJson, new TypeReference<ArrayList<SubmitTaskProcessRequestVO.TaskUser>>(){});
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        cursor.close();
        return taskUsers;
    }

    public void saveTaskUsers(List<SubmitTaskProcessRequestVO.TaskUser> taskUsers, String tnb) {
        String membersJson = null;
        try {
            membersJson = DataManager.getInstance().getMapper().writeValueAsString(taskUsers);
        } catch (IOException e) {
            e.printStackTrace();
        }
        if (S.isNotEmpty(membersJson)) {
            String sql = "INSERT OR REPLACE INTO " + BoxDBHelper.TABLE_TASKCHOOSEMEMBER +" (tnb, enb, members, time) values ('"
                    + tnb + "', '" + JavaBeanManager.getInstance().getUserinfo().getEnb() + "', '" + membersJson + "', '" + S.getNowTime3() + "')";
            db.execSQL(sql);
        }
    }
}
